package dartstools;

import java.sql.*;

/**
 *
 * @author Chris
 */
public class DatabaseSQLite {

    private static Connection conn;
    private static Statement stmt;
    private static PreparedStatement prepGeneral;

    private DatabaseSQLite() {
    }

    static {
        try {
            Class.forName("org.sqlite.JDBC");
            conn = DriverManager.getConnection("jdbc:sqlite:darts.sqlite");
            conn.setAutoCommit(false);
            stmt = conn.createStatement();
            dropTables(34687);
            createTables();
        }
        catch (ClassNotFoundException | SQLException e) {
            System.err.println(e.getMessage());
        }
    }

    private static void createTables() {
        try {
            stmt.executeUpdate("CREATE TABLE players("
                    + "playerid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
                    + "name VARCHAR(20) NOT NULL UNIQUE,"
                    + "added DATE DEFAULT CURRENT_TIMESTAMP);");
            stmt.executeUpdate("CREATE TABLE legs("
                    + "legid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
                    + "playerid1 INTEGER NOT NULL CONSTRAINT fk_foo_id REFERENCES players(playerid),"
                    + "playerid2 INTEGER NOT NULL CONSTRAINT fk_foo_id2 REFERENCES players(playerid) CHECK(playerid1 <> playerid2),"
                    + "added DATE DEFAULT CURRENT_TIMESTAMP);");
            stmt.executeUpdate("CREATE TABLE throws("
                    + "legid INTEGER REFERENCES leg(legid),"
                    + "playerid INTEGER REFERENCES players(playerid),"
                    + "dartgroupno INTEGER NOT NULL,"
                    + "added DATE DEFAULT CURRENT_TIMESTAMP,"
                    + "PRIMARY KEY(legid, playerid, dartgroupno));");
        }
        catch (SQLException e) {
            e.printStackTrace();
            System.err.println(e.getMessage());
        }
    }

    private static void dropTables(int dropKey) {
        if (dropKey == 34687) {
            try {
                stmt.executeUpdate("DROP TABLE players;");
                stmt.executeUpdate("DROP TABLE legs;");
                stmt.executeUpdate("DROP TABLE throws;");
            }
            catch (SQLException e) {
                System.err.println(e.getMessage());
            }
        }
    }

    public static void createPreparedStatement(String statement, int arg) {
        createPreparedStatement(statement, new Integer[]{arg});
    }

    public static void createPreparedStatement(String statement, String arg) {
        createPreparedStatement(statement, new String[]{arg});
    }

    public static void createPreparedStatement(String statement, Object[] args) {
        try {
            prepGeneral = conn.prepareStatement(statement);
            for (int i = 0; i < args.length; i++) {
                Object arg = args[i];
                if (arg instanceof String) {
                    prepGeneral.setString(i, (String) arg);
                }
                else if (arg instanceof Integer) {
                    prepGeneral.setInt(i, (Integer) arg);
                }
                else
                    throw new IllegalArgumentException("Unexpected argument object type: " + arg.getClass().getName() + " (\"" + arg + "\")");
            }
            prepGeneral.addBatch();
        }
        catch (SQLException e) {
            System.err.println(e.getMessage());
        }
    }

    public static void addUser(String name) {
        try {
            prepGeneral = conn.prepareStatement("INSERT INTO players(name) VALUES(?);");
            prepGeneral.setString(1, name);
            prepGeneral.executeUpdate();
            conn.commit();
        }
        catch (SQLException e) {
            System.err.println(e.getMessage());
        }
    }

    public static void addLeg(int playerid1, int playerid2) {
        try {
            prepGeneral = conn.prepareStatement("INSERT INTO legs(playerid1,playerid2) VALUES(?,?);");
            prepGeneral.setInt(1, playerid1);
            prepGeneral.setInt(2, playerid2);
            prepGeneral.executeUpdate();
            conn.commit();
        }
        catch (SQLException e) {
            System.err.println(e.getMessage());
        }
    }

    private static void printPlayersTable() {
        try {
            try (ResultSet rs = stmt.executeQuery("SELECT * FROM players;")) {
                System.out.println("playerid" + "\tname" + "\t\tadded");
                while (rs.next())
                    System.out.println(rs.getInt("playerid") + "\t\t" + rs.getString("name") + "\t\t" + rs.getString("added"));
            }
        }
        catch (SQLException e) {
            System.err.println(e.getMessage());
        }
    }

    private static void printLegsTable() {
        try {
            try (ResultSet rs = stmt.executeQuery("SELECT * FROM legs;")) {
                System.out.println("legid" + "\tplayerid1" + "\tplayerid2" + "\tadded");
                while (rs.next())
                    System.out.println(rs.getInt("legid") + "\t" + rs.getString("playerid1") + "\t\t" + rs.getString("playerid2") + "\t\t" + rs.getString("added"));
            }
        }
        catch (SQLException e) {
            System.err.println(e.getMessage());
        }
    }

    public static void main(String[] args) {
        try {
            addUser("Chris");
            addUser("Dad");
            printPlayersTable();
            System.out.println();
            addLeg(1, 23);
            printLegsTable();
            conn.close();
        }
        catch (SQLException e) {
            System.err.println(e.getMessage());
        }
    }

}